package com.dao;

import com.bean.Employee;
import com.common.DbConnect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class EmployeeDao {
    //public static void main(String[] args) {
    //     try {
    //         Class.forName("com.mysql.cj.jdbc.Driver");
    //     } catch (ClassNotFoundException e) {
    //         e.printStackTrace();
    //     }
    //
    //     try (
    //             Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT",
    //                     "root", "1234");
    //             Statement s = c.createStatement();
    //     )
    //     {
    //         System.out.print("lianjie....");
    //         String sql = "insert into l_user values('ID4'," + "'gailun'" + "," + "'1234'" + "," + 50 + ")";
    //         s.execute(sql);
    //
    //     } catch (SQLException e) {
    //         // TODO Auto-generated catch block
    //         e.printStackTrace();
    //     }
    // }
    //增加职工
    public void addEmployee(Employee employee){
        Connection cn= DbConnect.getConnection();
        PreparedStatement st = null;
        String sql = "insert into employee(enumber,epassword,ename,dnumber,on_the_job,manager)" +
                " values(?,?,?,?,?,?)";
        try {
            st = cn.prepareStatement(sql);

            st.setString(1, employee.getId());
            st.setString(2, employee.getPassword());
            st.setString(3, employee.getName());
            st.setString(4, employee.getDeptid());
            st.setBoolean(5,employee.isOnwork());
            st.setBoolean(6,employee.isIsmanager());

            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }
    }
    //删除职工
    public void deleteEmployee(String id){
        Connection cn= DbConnect.getConnection();
        PreparedStatement st = null;
        String sql = "delete from employee where enumber = " + id;
        try {
            st = cn.prepareStatement(sql);
            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }

    }
    //更改员工
    public void updateEmployee(Employee employee){
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        try {
            String sql = "update employee set epassword=?,ename=?,dnumber=?,on_the_job=?,manager=? where enumber=?";
            st = cn.prepareStatement(sql);

            st.setString(1, employee.getPassword());
            st.setString(2,employee.getName());
            st.setString(3,employee.getDeptid());
            st.setBoolean(4,employee.isOnwork());
            st.setBoolean(5,employee.isIsmanager());
            st.setString(6, employee.getId());

            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }
    }
    //个体查询
    public Employee getEmployee(String id){
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            String sql = "select * from employee where enumber=?";
            st = cn.prepareStatement(sql);
            st.setString(1, id);

            rs = st.executeQuery();
            if (rs.next()) {//找到
                Employee employee = new Employee(rs.getString("enumber"),rs.getString("ename")
                        ,rs.getString("epassword"),rs.getString("dnumber")
                        , rs.getBoolean("on_the_job"), rs.getBoolean("manager"));

                return employee;
            } else {//未找到
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return null;
    }
    //列表查询,按部门
    public List<Employee> getEmployeeListByDept(String deptid){
        ArrayList<Employee> employees = new ArrayList<Employee>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            String sql = "select * from employee where dnumber=?";
            st = cn.prepareStatement(sql);
            st.setString(1, deptid);

            rs = st.executeQuery();
            while (rs.next()) {
                Employee employee = new Employee(rs.getString("enumber"),rs.getString("ename")
                        ,rs.getString("epassword"),rs.getString("dnumber")
                        , rs.getBoolean("on_the_job"), rs.getBoolean("manager"));
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return employees;
    }
    //列表查询,全体职工
    public List<Employee> getEmployeeList(){
        ArrayList<Employee> employees = new ArrayList<Employee>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            String sql = "select * from employee";
            st = cn.prepareStatement(sql);

            rs = st.executeQuery();
            while (rs.next()) {
                Employee employee = new Employee(rs.getString("enumber"),rs.getString("ename")
                        ,rs.getString("epassword"),rs.getString("dnumber")
                        , rs.getBoolean("on_the_job"), rs.getBoolean("manager"));
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return employees;
    }
}
